package com.xnyzc.lhy.mis.mapper.vehicle;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xnyzc.lhy.mis.entity.result.cms.driver.CmsDriverSelect;
import com.xnyzc.lhy.mis.entity.vehicle.OaSysVehicle;
import com.xnyzc.lhy.mis.entity.result.cms.veh.VehicleManageInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 车辆信息表 Mapper 接口
 * </p>
 *
 * @author bin
 * @since 2019-07-22
 */
public interface OaSysVehicleMapper extends BaseMapper<OaSysVehicle> {


    /**
     * 查询车辆详情信息
     *
     * @param vehicleId
     * @return
     */
    @Select("SELECT " +
            "oa_sys_vehicle.vehicle_id, " +
            "oa_sys_vehicle.veh_no, " +
            "oa_sys_vehicle.veh_color, " +
            "oa_sys_vehicle.code, " +
            "oa_sys_vehicle.veh_engine_number, " +
            "oa_sys_vehicle.veh_frame_number, " +
            "oa_sys_vehicle.veh_fuel_type, " +
            "oa_sys_vehicle.vehicle_picture_id, " +
            "oa_sys_vehicle.vehicle_license_picture_id, " +
            "oa_sys_vehicle.city_code, " +
            "oa_sys_vehicle.driving_license_number, " +
            "oa_sys_vehicle.vehicle_owner_id, " +
            "oa_sys_vehicle.transport_certificate_number, " +
            "oa_sys_vehicle.vehicle_license_picture_back_id, " +
            "oa_sys_vehicle.vehicle_license_supplementary_page_picture_id, " +
            "oa_sys_vehicle.transport_certificate_picture_id, " +
            "oa_d_user.driver_id, " +
            "oa_d_user.driver_name, " +
            "oa_d_user.driver_phone, " +
            "oa_sys_dict.label as color," +
            "oa_sys_vehicle_mdict.veh_seats_number," +
            "oa_sys_vehicle_mdict.vehicle_type," +
            "oa_sys_vehicle_mdict.is_relation," +
            "oa_sys_vehicle_mdict.mdict_id ,    " +
            "oa_sys_vehicle.vehicle_type  as vehicletype1,    " +
            "oa_sys_vehicle.owner_name ,    " +
            "oa_sys_vehicle.vin ,    " +
            "oa_sys_vehicle.certigy_date_a ,    " +
            "oa_sys_vehicle.fuel_type ,    " +
            "oa_sys_vehicle.engine_displace ,    " +
            "oa_sys_vehicle.trans_agency ,    " +
            "oa_sys_vehicle.trans_area ,    " +
            "oa_sys_vehicle.trans_date_start ,    " +
            "oa_sys_vehicle.trans_date_stop ,    " +
            "oa_sys_vehicle.certify_date_b ,    " +
            "oa_sys_vehicle.fix_stat ,    " +
            "oa_sys_vehicle.check_stat ,    " +
            "oa_sys_vehicle.fee_print_id ,    " +
            "oa_sys_vehicle.gps_brand ,    " +
            "oa_sys_vehicle.gps_model ,    " +
            "oa_sys_vehicle.gps_install_date ,    " +
            "oa_sys_vehicle.register_date ,    " +
            "oa_sys_vehicle.commercial_type     " +
            "FROM " +
            "oa_sys_vehicle  " +
            "LEFT JOIN oa_d_driver_vehicle ON oa_sys_vehicle.vehicle_id = oa_d_driver_vehicle.vehicle_id " +
            "AND oa_d_driver_vehicle.deleted = 0    " +
            "LEFT JOIN oa_d_user ON oa_d_driver_vehicle.driver_id  = oa_d_user.driver_id  " +
            "AND oa_d_user.deleted = 0  " +
            "LEFT JOIN oa_sys_dict ON oa_sys_vehicle.veh_color = oa_sys_dict.value " +
            "AND oa_sys_dict.deleted = 0    " +
            "AND oa_sys_dict.type = 'vegicle_color_type'   " +
            "LEFT JOIN oa_sys_vehicle_mdict ON oa_sys_vehicle.code = oa_sys_vehicle_mdict.code  " +
            "AND oa_sys_vehicle_mdict.deleted = 0   " +
            "WHERE " +
            "oa_sys_vehicle.deleted = 0  " +
            "AND oa_sys_vehicle.vehicle_id = #{vehicleId}")
    List<VehicleManageInfo> getVehicleDetails(@Param("vehicleId") Long vehicleId);


    /**
     * 分页模糊查询车辆信息
     *
     * @param iPage：分页器
     * @param vehNo：车牌号
     * @param cityCode：城市code
     * @param vehFrameNumber：车架号
     * @param code：车辆code
     * @param driveName：司机姓名
     * @return
     */
    @Select({"<script> " +
            "SELECT   " +
            "oa_sys_vehicle.vehicle_id, " +
            "oa_sys_vehicle.veh_no, " +
            "oa_sys_vehicle.veh_frame_number, " +
            "oa_sys_vehicle.code, " +
            "oa_sys_vehicle.city_code, " +
            "oa_sys_dict.label as color," +
            "oa_d_user.driver_id, " +
            "oa_d_user.driver_name, " +
            "oa_d_user.driver_phone    " +
            "FROM " +
            "oa_sys_vehicle  " +
            "LEFT JOIN oa_d_driver_vehicle ON oa_sys_vehicle.vehicle_id = oa_d_driver_vehicle.vehicle_id  " +
            "AND oa_d_driver_vehicle.deleted = 0   " +
            "LEFT JOIN oa_d_user ON oa_d_driver_vehicle.driver_id = oa_d_user.driver_id  " +
            "AND oa_d_user.deleted = 0   " +
            "LEFT JOIN oa_sys_dict ON oa_sys_vehicle.veh_color = oa_sys_dict.value " +
            "AND oa_sys_dict.deleted = 0  " +
            "AND oa_sys_dict.type = 'vegicle_color_type'   " +
            "LEFT JOIN oa_sys_vehicle_mdict ON oa_sys_vehicle.code = oa_sys_vehicle_mdict.code  " +
            "AND oa_sys_vehicle_mdict.deleted = 0   " +
            "AND oa_sys_vehicle_mdict.is_relation = 1 " +
            "<if test=' city_code.size() != 0'  > " +
            "LEFT JOIN oa_sys_area ON oa_sys_vehicle.city_code = oa_sys_area.ad_code  " +
            "AND oa_sys_area.deleted = 0   " +
            "</if>   " +
            "WHERE " +
            "oa_sys_vehicle.deleted = 0  " +
            "<if test=' veh_no.length() != 0'  >" +
            "AND oa_sys_vehicle.veh_no LIKE CONCAT('%',#{veh_no},'%')    " +
            "</if>  " +
            "<if test=' veh_frame_number.length() != 0'  >" +
            "AND oa_sys_vehicle.veh_frame_number LIKE CONCAT('%',#{veh_frame_number},'%')    " +
            "</if>  " +
            "<if test='drive_name.length() != 0'  >" +
            "AND oa_d_user.driver_name LIKE CONCAT('%',#{drive_name},'%') " +
            "</if>  " +
            "<if test=' city_code.size() != 0'  > " +
            "AND  oa_sys_vehicle.city_code  IN" +
            "<foreach collection='city_code' item='item'  index='index' open='(' separator=',' close=')'>" +
            "#{item}   " +
            "</foreach>   " +
            "</if> " +
            "<if test=' code.size() != 0' > " +
            "AND  oa_sys_vehicle.code  in" +
            "<foreach collection='code' item='item'  index='index' open='(' separator=',' close=')'>" +
            "#{item}   " +
            "</foreach>   " +
            "</if> " +

            "AND  oa_sys_vehicle.vehicle_owner_id  in" +
            "<foreach collection='owners' item='item'  index='index' open='(' separator=',' close=')'>" +
            "#{item}   " +
            "</foreach>   " +

            "ORDER BY   " +
            "   oa_sys_vehicle.create_time DESC " +
            "</script>"})
    List<VehicleManageInfo> getVehicleList(IPage iPage,
                                           @Param("veh_no") String vehNo,
                                           @Param("city_code") List<String> cityCode,
                                           @Param("veh_frame_number") String vehFrameNumber,
                                           @Param("code") List<String> code,
                                           @Param("drive_name") String driveName,
                                           @Param("owners") List<Long> owners
    );

    @Select("select " +
            "driver_id as value ," +
            "driver_name as label " +
            "from oa_d_user " +
            "where deleted = 0")
    List<CmsDriverSelect> getDriverSelect();


    /**
     * 获取车辆信息Excel 导出
     *
     * @return
     */
    @Select(
            "<script>  SELECT " +
                    "oa_sys_vehicle.vehicle_id, " +
                    "oa_sys_vehicle.veh_no, " +
                    "oa_sys_vehicle.veh_frame_number, " +
                    "oa_sys_vehicle.code, " +
                    "oa_sys_vehicle.city_code, " +
                    "oa_sys_vehicle.veh_engine_number, " +
                    "oa_sys_vehicle.driving_license_number, " +
                    "oa_sys_vehicle.transport_certificate_number, " +
                    "oa_sys_vehicle.veh_fuel_type, " +
                    "oa_sys_dict.label as color," +
                    "oa_d_user.driver_id, " +
                    "oa_d_user.driver_name, " +
                    "oa_d_user.driver_phone," +
                    "oa_sys_vehicle_mdict.veh_seats_number    " +
                    "FROM " +
                    "oa_sys_vehicle  " +
                    "LEFT JOIN oa_d_driver_vehicle ON oa_sys_vehicle.vehicle_id = oa_d_driver_vehicle.vehicle_id  " +
                    "AND oa_d_driver_vehicle.deleted = 0  " +
                    "LEFT JOIN oa_d_user ON oa_d_driver_vehicle.driver_id  = oa_d_user.driver_id " +
                    "AND oa_d_user.deleted = 0   " +
                    "LEFT JOIN oa_sys_dict ON oa_sys_vehicle.veh_color = oa_sys_dict.value  " +
                    "AND oa_sys_dict.deleted = 0     " +
                    "AND oa_sys_dict.type = 'vegicle_color_type' " +
                    "LEFT JOIN oa_sys_vehicle_mdict ON oa_sys_vehicle.code = oa_sys_vehicle_mdict.code " +
                    "AND oa_sys_vehicle_mdict.deleted = 0    " +
                    "AND oa_sys_vehicle_mdict.is_relation = 1 " +
                    "<if test=' city_code.size() != 0'  > " +
                    "LEFT JOIN oa_sys_area ON oa_sys_vehicle.city_code = oa_sys_area.ad_code  " +
                    "AND oa_sys_area.deleted = 0   " +
                    "</if>   " +
                    "WHERE " +
                    "oa_sys_vehicle.deleted = 0  " +
                    "<if test=' veh_no.length() != 0'  >" +
                    "AND oa_sys_vehicle.veh_no LIKE CONCAT('%',#{veh_no},'%')    " +
                    "</if>  " +
                    "<if test=' veh_frame_number.length() != 0'  >" +
                    "AND oa_sys_vehicle.veh_frame_number LIKE CONCAT('%',#{veh_frame_number},'%')    " +
                    "</if>  " +
                    "<if test='drive_name.length() != 0'  >" +
                    "AND oa_d_user.driver_name LIKE CONCAT('%',#{drive_name},'%') " +
                    "</if>  " +
                    "<if test=' city_code.size() != 0'  > " +
                    "AND  oa_sys_vehicle.city_code  IN" +
                    "<foreach collection='city_code' item='item'  index='index' open='(' separator=',' close=')'>" +
                    "#{item}   " +
                    "</foreach>   " +
                    "</if> " +
                    "<if test=' code.size() != 0' > " +
                    "AND  oa_sys_vehicle.code  in" +
                    "<foreach collection='code' item='item'  index='index' open='(' separator=',' close=')'>" +
                    "#{item}   " +
                    "</foreach>   " +
                    "</if> " +
                    "ORDER BY " +
                    "oa_sys_vehicle.create_time DESC  </script>")
    List<VehicleManageInfo> exportGetVehicle(
            @Param("veh_no") String vehNo,
            @Param("city_code") List<String> cityCode,
            @Param("veh_frame_number") String vehFrameNumber,
            @Param("code") List<String> code,
            @Param("drive_name") String driveName
    );


    /**
     * 更换车辆查询
     *
     * @return
     */
    @Select("<script> SELECT   " +
            "oa_sys_vehicle.vehicle_id, " +
            "oa_sys_vehicle.veh_no, " +
            "oa_sys_vehicle.code, " +
            "oa_sys_dict.label as color " +
            "FROM " +
            "oa_sys_vehicle  " +
            "LEFT JOIN oa_sys_dict ON oa_sys_vehicle.veh_color = oa_sys_dict.value " +
            "AND oa_sys_dict.deleted = 0  " +
            "AND oa_sys_dict.type = 'vegicle_color_type'   " +
            "LEFT JOIN oa_sys_vehicle_mdict ON oa_sys_vehicle.code = oa_sys_vehicle_mdict.code  " +
            "AND oa_sys_vehicle_mdict.deleted = 0   " +
            "AND oa_sys_vehicle_mdict.is_relation = 1 " +
            "WHERE " +
            "oa_sys_vehicle.deleted = 0  " +
            "AND oa_sys_vehicle.vehicle_owner_id=0  " +
            "<if test=' veh_no.length() != 0'  >" +
            "AND oa_sys_vehicle.veh_no LIKE CONCAT('%', #{veh_no}, '%')    " +
            "</if>  " +
            "ORDER BY   " +
            "   oa_sys_vehicle.create_time DESC </script>")
    List<VehicleManageInfo> changeVehicleList(IPage iPage,
                                              @Param("veh_no") String vehNo);

}
